Shareware Grab Bag/Shareware Grab Bag.iso/019/see123.pas

Pascal/Delphi Source File | 1986-09-24 | 20.3 KB | 635 lines

Program LotusLOOK; (* A Program to read and partially decode LOTUS123 files *)

(*
  The purpose of this example program is to provide the information needed
  to create and interpret 1-2-3 data files from within a Turbo Pascal
  program.

  This file contains the type definitions and procedures necessary to read
  and write Lotus 1-2-3 WKS files. The program will work with versions
  1,1a and 2 of 1-2-3.

  The information in this program was developed from two sources; First,
  a handout which was distributed at the Lotus Developer's conference
  held by Lotus, and second, plain old trial and error. The information
  distributed at the conference gave the name of each type of record
  and the number of bytes normally contained by that record. Trial and
  error was used to determine how the information in the records was to
  be interpreted. In the case of some record types, no attempt has been
  made to interpret the data. For instance, the definition of graph
  records has been defined as an array of bytes without regard for
  the actual function of those bytes.

  A Lotus 1-2-3 File consists of a series of variable length records.
  The first two bytes of each record represent an integer which gives
  the record Type, the third and fourth bytes contain an integer which
  represents the number of bytes in the record (exclusive of the type
  and length bytes).

  Columns and Rows within the worksheet are numbered starting with 0.
  Thus, cell A1 in the spreadsheet is Cell[0,0] in the matrix, cell B2
  is Cell[1,0]. Cells are written to the file in sequence working across
  each row, i.e., all the cells in row 0 are written, then all cells in
  row 1, etc.

  Each record describing a cell contains a format byte, the column and
  row where it fits in the matrix and the contents of that cell, be it
  integer, floating point, text, or fomula. Cells containing dates
  are treated as integer cells. Text information is stored in the form
  of ASCII Z strings. Unlike Turbo strings where byte[0] gives the length
  of the string, ASCIIZ strings are arrays of characters with a character
  #0 representing the end of the string. The format byte consists of two
  4 bit fields, one of which is used to flag the format type--Fixed, Currency,
  Date, etc.  The other is used to denote the decimal places to be used, or
  the number of the date type. Experiment with files of your own creation,
  and you will be able to see the relationships.

  The basic 1-2-3 record types are:

  0 : Beginning of File - 2 Data Bytes;
  1 : End of File - 0 Data Bytes;
  2 : Method of Calculation - 1 Data Byte;
  3 : Calculation order - 1 Data Byte;
  4 : Window Split Indicator - 1 Data Byte;
  5 : Window Synchronization Indicator - 1 Data Byte;
  6 : WorkSheet Dimensions - Number of Columns, Number of Rows : Integers;
 *7 : First Window Data - Varies depending on version number
  8 : Column width overrides - Column number, Column width;
      A column width record exists for any column for which the
      user has established a width other than the default;
 *9 : Second Window Data - Varies depending on version number;
  10 : Column Width Overrides for window two;
  11 : Named Range : Title, Column,Row of upper corner, Column,Row of lower
       corner;
  12 : Blank Cell : Format Byte, Column, Row; A cell with no contents
  13 : Integer Cell : Format Byte, Column, Row, cell contents;
  14 : Floating Point Cell : Format Byte, Column, Row, IEEE Real Number;
  15 : Label Cell : Format Byte, Column, Row, Contents
  16 : Formula Cell : Format Byte, Column, Row, Contents
 *24 : Data Table Range : 25 Bytes representing the coordinates of the range;
 *25 : Query Range : 25 bytes representing the coordinate;
 *26 : Print Range : Column and row of the upper and lower corners of the range
 *27 : Sort Range : Column and row of the upper and lower corners of the range
 *28 : Fill Range : Column and row of the upper and lower corners of the range
 *29 : Primary Sort Key : Coordinates of top and bottom of the range,
       Byte denoting ascending or descending sort
 *32 : Distribution range : Coordinates of the range;
 *35 : Secondary Sort Key : Coordinates of top and bottom of the range,
       Byte denoting ascending or descending sort
  36 : Global Protection : Byte
  37 : Footer : ASCII Z string for page footer;
  38 : Header : ASCII Z string for page header;
  39 : Setup String : ASCII Z string for printer setup;
 *40 : Print Margins : Left, Rigth Margin, Page Length, Top , Bottom Margin
  41 : Global Label Alignment Character : Character (',",^);
 *42 : Borders : Coordinates of print borders;
 *45 : Current Graph Information : 437 Bytes;
 *46 : Named Graph Information : 453 Bytes;
  47 : Iteration Count : Byte;
  48 : Print Format Flag : Byte;
  49 : Active Window Flag : Byte;

  * denotes definitions where the ordering of information has not yet been
    established.

An "Empty File" (No data in the cells yet) as created by Lotus 1-2-3
contains the following records:

   0 - Begin File Information
   6 - Spreadsheet Dimensions
  47 - Iteration setting
   2 - Calculation method
   3 - Calculation order
   4 - Split Window Flag
   5 - Window Synchronization Flag
   7 - Window 1 Data
  24 - Data Table information
  25 - Query Range information
  26 - Print Range information
  48 - Print format flag
  28 - Data Fill range information
  27 - Data Sort Range information
  29 - Primary Sort Range
  35 - Secondary Sort Range
  32 - Distribution Range
  36 - Protection Flag
  37 - Footer
  38 - Header
  39 - Setup string
  40 - Margins
  41 - Global label alignment character
  42 - Print borders
  45 - Graph settings

Although 1-2-3 places these records in an empty file, my tests have shown that
a file containing just the BeginFile record, the Worksheet Dimensions record,
then the various cells to be placed in the file and then the EndFile record
will be loaded properly by 1-2-3. This appears to offer a more streamlined
method of creating files which can be used by 1-2-3; plus it saves all the
disk file space which is occupied by those default records.

It appears that in the various range records the byte 255 is used to indicate
that the range has not yet been set.

In order to read a 1-2-3 file, one merely opens the file and starts reading
the record type bytes, after the record type has been read the next two
bytes will tell you how many data bytes there are in the record. Read the
specified number of bytes into a buffer and then apply the proper interp-
retation to those bytes. In order to facilitate this process a Pascal
record with variant parts has been defined.

The primary need of a Turbo programmer will be to transfer a data base
or other data from a Turbo application to a 1-2-3 application. By writing
directly to a 1-2-3 format file one can spare the user of the program
the burden of dumping the data to a DIF or Text file and then using the
1-2-3 translate programs to place the data in a 1-2-3 worksheet file which,
by the way, do not behave too well when text and numeric data are mixed.

Preparing the Turbo generated 1-2-3 file requires that the file be
filled with the default values for the records found in all files.
Then the actual data base information would be written to the file
one row (record) at a time, making the proper conversions from
Turbo strings and turbo reals. Dates, of course, would have to be
converted to integers if the application will call for manipulation
of the dates within 1-2-3. Several programs in the Borland SIG DL1
contain routines to convert dates to integers based on various starting
dates.

Comments, improvements, etc. can be addressed to:
    Gary Macker, CPA
    Compuserve ID 76314,2747

*)

Type
  String16 = String[16];

  ByteFile = File of Byte;

  ZType = Array[1..255] of Char; (* ASCII Z String *)

  AnyString = String[255];

  IEEERealType = Array[1..8] of byte;

  TurboRealType = Array[1..6] of byte;

  MantissaType = Array[1..5] of byte;

  BitType = (B0,B1,B2,B3,B4,B5,B6,B7);

  BitSetType = Set of B0..B7;

  WksDimType = Record
    UpLftCol : Integer; (* Home *)
    UpLftRow : Integer;
    LowRtCol : Integer; (* End Home *)
    LowRtRow : Integer
  End;

  WinDataType = Record
    Col : Integer;
    Width : Byte;
  End;

  RangeRecType = Record
    Name : Array[1..16] of Char; (* An ASCIIZ String *)
    Col1,Row1,Col2,Row2 : Integer;
  End;

  BlankCellType = Record
    Format : BitSetType;
    Col,Row : Integer;
  End;

  IntegerCellType = Record
    Format : BitSetType;
    Col,Row,Num : Integer;
  End;

  RealCellType = Record
    Format : BitSetType;
    Col,Row : Integer;
    Num : IEEERealType;
  End;

  LabelCellType = Record
    Format : BitSetType;
    Col,Row : Integer;
    CellLabel : ZType;
  End;

  FormulaCellType = Record
    Format : BitSetType;
    Col,Row : Integer;
    LastValue : IEEERealType;
    Formula : Array[1..255] of Byte;
  End;

  LotusRecType = Record
    RecType : Integer;
    RecSize : Integer;
    Case Integer of
    0 : (Flag1,Flag2 : Byte);
    2 : (CalcMethod : Byte);
    3 : (CalcOrder : Byte);
    4 : (SplitWindow : Byte);
    5 : (WindowSynch : Byte);
    6 : (WksDim : WksDimType);
    7 : (FirstWindowFlags : Array[1..32] of Byte); (* 31 bytes in Ver 1,1A; 32 in Ver 2 *)
    8 : (WinOneData : WinDataType);
    9 : (SecondWindowFlags : Array[1..32] of Byte); (* 31 bytes in Ver 1,1A; 32 in Ver 2 *)
    10 : (WinTwoData : WinDataType);
    11 : (RangeRec : RangeRecType);
    12 : (BlankCell : BlankCellType);
    13 : (IntegerCell : IntegerCellType);
    14 : (RealCell : RealCellType);
    15 : (LabelCell : LabelCellType);
    16 : (FormulaCell : FormulaCellType);
    24 : (TableData : Array[1..25] of Byte);
    25 : (QueryRangeData : Array[1..25] of Byte);
    26 : (PrintRangeCol1,PrintRangeRow1,PrintRangeCol2,PrintRangeRow2 : Integer);
    27 : (SortRangeData : Array[1..4] of Integer);
    28 : (FillRangeData : Array[1..4] of Integer);
    29 : (PrimSortData : Array[1..9] of Byte);
    32 : (DistRangeData : Array[1..16] of Byte);
    35 : (SecSortData : Array[1..9] of Byte);
    36 : (ProtectOn : Byte);
    37 : (Footer : Array[1..242] of Char);
    38 : (Header : Array[1..242] of Char);
    39 : (SetupString : Array[1..40] of Char);
    40 : (LeftM,RightM,PageL,BottM,TopM : Integer);
    41 : (LabelPrefix : Char);
    42 : (BorderRange : Array[1..8] of Integer);
    45 : (GraphData : Array[1..437] of Integer);
    46 : (NamedGraphData : Array[1..453] of Integer);
    47 : (Iteration : Byte);
    48 : (PrintFormat : Byte);
    49 : (ActiveWindow : Byte);
  End;

Var
  LotusRec : LotusRecType;
  RecordBuffer : Array[1..455] of Byte Absolute LotusRec;
  F : ByteFile;
  FileName : String[60];
  C,Ch : Char;

Const
  RecNames : Array[0..$31] of String[25] = (
  'Begin File',
  'End File',
  'Calculation Method',
  'Calculation Order',
  'Split Window',
  'Window Synch',
  'Worksheet Dimensions',
  'First Window',
  'Col Width Overrides/1',
  'Second Widow',
  'Col Width Overrides/2',
  'Named Range',
  'Blank Cell',
  'Integer Cell',
  'Floating Point Cell',
  'Label Cell',
  'Formula Cell',
  '',
  '',
  '',
  '',
  '',
  '',
  '',
  'Table Range',
  'Query Range',
  'Print Range',
  'Sort Range',
  'Fill Range',
  'Primary Key',
  '',
  '',
  'Distribution Range',
  '',
  '',
  'Secondary Key',
  'Global Protection',
  'Print Footer',
  'Print Header',
  'Setup String',
  'Print Margins',
  'Global Label',
  'Print Borders',
  '',
  '',
  'Current Graph Settings',
  'Named Graph',
  'Iteration Count',
  'Print Format Mode',
  'Active Window');

  Zero : Byte = 0;
  One : Byte = 1;
  two : Byte = 2;
  Three : Byte = 3;
  Four : Byte = 4;
  Five : Byte = 5;
  Six : Byte = 6;
  Seven : Byte = 7;
  Eight : Byte = 8;
  Nine : Byte = 9;
  Ten : Byte = 10;
  Eleven : Byte = 11;
  Twelve : Byte = 12;
  Thirteen : Byte = 13;
  Fourteen : Byte = 14;

Function IeeeToTurbo(long : IEEERealType) : Real;

(*
 * This function was adapted from information supplied by Borland for use
 * in reading and writing Reflex data files. Note that the routine as
 * set forth in that file did not handle the conversion properly when
 * the number was 0.00. It added the bias to the exponent, which then
 * made the converted number non-zero. A zero exponent (absolute zero,
 * not zero after correcting for the sign bit) denotes 0.00 in both
 * the IEEE and Turbo 6 byte formats. A secondary result of the way
 * in which I have altered the routine is that if the exponent falls
 * outside the possible range for a Turbo real, the number will be
 * converted to 0.00. You may want to institute different error
 * handling in your application
 *)

Var
  R : Real;
  I : Integer;
  E : byte;
  T : TurboRealType Absolute R;
  Sign : Byte;

Begin
  FillChar(R,SizeOf(R),0);
  I := (long[8] and $7f) shl 4;
  I := I or ((long[7] and $f0) shr 4);
  if (I < 985) or (I > 1061) then T[1] := 0
  Else
  Begin
    I := I - 1023;
    T[1] := I + $81;
  End;
  Sign := long[8] and $80;
  T[6] := sign + ((long[7] and $0f) shl 3) or ((long[6] and $e0) shr 5);
  for I := 5 downto 2 do
    t[I] := ((long[I+1] and $1f) shl 3) or ((long[I] and $e0) shr 5);
  If T[1] <> 0 Then IEEEtoTurbo := R Else R := 0;
End;

Procedure TurboToIEEE(Var TN : Real; Var IEEE : IEEERealType);
Var
  TR : TurboRealType Absolute TN;
  E : Integer;
  Sign : Byte;
Begin
  FillChar(IEEE,SizeOf(IEEE),0); (* Start with a clean slate *)
  If TR[1] <> 0 Then (* Zero means 0.00 *)
  Begin
    Sign := TR[6] and $80;
    E := TR[1] - $81;
    E := E + 1023;
    IEEE[8] := Sign + (E Shr 4);
    IEEE[7] := E Shl 4;
    TR[6] := TR[6] or $80;
    IEEE[7] := IEEE[7] + ((TR[6] and $78) Shr 3);
    IEEE[6] := ((TR[6] and $07) Shl 5) + ((TR[5] and $F8) Shr 3);
    IEEE[5] := ((TR[5] and $07) Shl 5) + ((TR[4] and $F8) Shr 3);
    IEEE[4] := ((TR[4] and $07) Shl 5) + ((TR[3] and $F8) Shr 3);
    IEEE[3] := ((TR[3] and $07) Shl 5) + ((TR[2] and $F8) Shr 3);
    IEEE[2] := ((TR[2] and $07) Shl 5) + (($0 and $F8) Shr 3);
    IEEE[1] := 0; (* Least significant bits made 0 *)
  End;
End;

Procedure GetName;
Var
  I : Integer;
Begin
  ClrScr;
  Write('ENTER THE NAME OF THE FILE YOU WANT TO DECODE :');
  ReadLn(FileName);
  If FileName = '' Then Halt;
  For I := 1 to Length(FileName) Do FileName[I] := UpCase(FileName[I]);
  If Pos('.',FileName) = 0 Then FileName := FileName +'.WKS';
End;

Function OpenFile(FN : AnyString) : Boolean;
Var
  B : Boolean;
Begin
  Assign(F,FN);
  {$I-}
  Reset(F);
  B := (IOResult = 0);
  {$I+}
  If Not B Then Close(F);
  OpenFile := B;
End;

Procedure ReadLotusRec(Var F : ByteFile); (* Assumes that the file pointer is at the start of a record *)
Var
  I : Integer;
Begin
  For I := 1 to 4 Do Read(F,RecordBuffer[I]);
  For I := 5 to LotusRec.RecSize+4 Do Read(F,RecordBuffer[I]);
End;

Procedure WriteZ(Var Z);
Var
  ZString : ZType Absolute Z;
  I : Byte;
Begin
  I := 1;
  While ZString[I] > #0 Do
  Begin
    Write(ZString[I]);
    I := Succ(I);
  End;
End;

Function BitRep(B : BitSetType) : String16;
Var
  S : String16;
  Bit : BitType;
Begin
  S := '';
  For Bit := B7 downto B0 Do If Bit in B Then S := S +'1 ' Else S := S + '0 ';
  BitRep := S;
End;

Procedure PrintRec;
Var
  I : Integer;
  R : Real;
Begin
  WriteLn('Record Type   = ',LotusRec.RecType,'  ',RecNames[LotusRec.RecType]);
  WriteLn('Size in Bytes = ',LotusRec.RecSize);
  WriteLn('Record Contents:');
  With LotusRec Do Case RecType of
    0 : Write(Flag1:4,Flag2:4);
    1 : Begin End;
    2 : Write(CalcMethod);
    3 : Write(CalcOrder);
    4 : Write(SplitWindow);
    5 : Write(WindowSynch);
    6 : With WksDim Do Begin
         Write(UpLftCol:4, UpLftRow:4, LowRtCol:4, LowRtRow:4);
        End;
    7 : For I := 1 to RecSize Do Write(FirstWindowFlags[I]:4);
    8 : With WinOneData Do Write('Column ',Col,' is ',Width,' Spaces Wide');
    9 : For I := 1 to RecSize Do Write(SecondWindowFlags[I]:4);
    10 : With WinTwoData Do Write('Column ',Col,' is ',Width,' Spaces Wide');
    11 : With RangeRec Do Begin
           WriteZ(Name);
           WriteLn;
           Write('Column 1 ',Col1,' Row 1 ',Row1,' Column 2 ',Col2,' Row 2 ',Row2);
         End;
    12 : With BlankCell Do Begin
           WriteLn('Format Byte = ',BitRep(Format));
           Write('Column ',Col,' Row ',Row);
         End;
    13 : With IntegerCell Do Begin
           WriteLn('Format Byte = ',BitRep(Format));
           WriteLn('Column ',Col,' Row ',Row);
           Write('Integer Value is ',Num);
         End;
    14 : With RealCell Do Begin
           WriteLn('Format Byte = ',BitRep(Format));
           WriteLn('Column ',Col,' Row ',Row);
           R := IEEEtoTurbo(Num);
           Write('Cell Value is ',R:15:2);
         End;
    15 : With LabelCell Do Begin
           WriteLn('Format Byte = ',BitRep(Format));
           WriteLn('Column ',Col,' Row ',Row);
           WriteZ(CellLabel);
        End;
    16 : With FormulaCell Do Begin
           WriteLn(BitRep(Format));
           WriteLn('Column ',Col,' Row ',Row);
           I := 1;
           R := IEEEtoTurbo(LastValue);
           WriteLn('Last Computed Value is ',R:15:2);
           For I := 1 to (LotusRec.RecSize - 8) Do Write(Formula[I]:4);
         End;
    24 : For I := 1 to RecSize Do Write(TableData[I]:4);
    25 : For I := 1 to RecSize Do Write(QueryRangeData[I]:4);
    26 : Write(PrintRangeCol1:4,PrintRangeRow1:4,PrintRangeCol2:4,PrintRangeRow2:4);
    27 : For I := 1 to 4 Do Write(SortRangeData[I]:4);
    28 : For I := 1 to 8 Do Write(FillRangeData[I]:4);
    29 : For I := 1 to RecSize Do Write(PrimSortData[I]:4);
    32 : For I := 1 to RecSize Do Write(DistRangeData[I]:4);
    35 : For I := 1 to 9 Do Write(SecSortData[I]:4);
    36 : Write(ProtectOn);
    37 : WriteZ(Footer);
    38 : WriteZ(Header);
    39 : WriteZ(SetupString);
    40 : Write('Left ',LeftM,' Right ',RightM,' Top ',TopM,' Bottom ',BottM,' Page Length ',PageL);
    41 : Write(LabelPrefix);
    42 : For I := 1 to 8 Do Write(BorderRange[I]:4);
    45 : For I := 1 to 437 Do Write(GraphData[I]:4);
    46 : For I := 1 to 453 Do Write(NamedGraphData[I]:4);
    47 : Write(Iteration);
    48 : Write(PrintFormat);
    49 : Write(ActiveWindow);
  End;
  WriteLn;
End;

Procedure WriteBeginFile(Var F : ByteFile);
Begin
  Write(F,Zero,Zero,Two,Zero,Four,Four);
  (* Record Type 0, Record Size 2, ??? 4, 4 *)
End;

Procedure WriteWksSize(Var F : ByteFile; Cols,Rows : Integer);
Var
  B : Byte;
Begin
  Write(F,Six,Zero,Eight,Zero,Zero,Zero,Zero,Zero);
  B := Lo(Cols);
  Write(F,B);
  B := Hi(Cols);
  Write(F,B);
  B := Lo(Rows);
  Write(F,B);
  B := Hi(Rows);
  Write(F,B);
End;

Procedure WriteRealCell(Var F : ByteFile; FormatByte : Byte; Col,Row : Integer; Value : Real);
Var
  B : Byte;
  IE : IEEERealType;

Begin
  Write(F,Fourteen,Zero,Thirteen,Zero,FormatByte);
  B := Lo(Col);
  Write(F,B);
  B := Hi(Col);
  Write(F,B);
  B := Lo(Row);
  Write(F,B);
  B := Hi(Row);
  Write(F,B);
  TurboToIEEE(Value,LotusRec.RealCell.Num);
  For B := 1 to 8 Do Write(F,LotusRec.RealCell.Num[B]);
End;

Procedure WriteIntegerCell(Var F : ByteFile; FormatByte : Byte; Col,Row,Value : Integer);
Var
  B : Byte;
Begin
  Write(F,Thirteen,Zero,Seven,Zero,FormatByte);
  B := Lo(Col);
  Write(F,B);
  B := Hi(Col);
  Write(F,B);
  B := Lo(Row);
  Write(F,B);
  B := Hi(Row);
  Write(F,B);
  B := Lo(Value);
  Write(F,B);
  B := Hi(Value);
  Write(F,B);
End;

Procedure EndLotusFile(Var F : ByteFile);
Begin
  Write(F,One,Zero,Zero,Zero);
  Close(F);
End;

Begin
  Repeat GetName Until OpenFile(FileName);
  Repeat
    ReadLotusRec(F);
    PrintRec;
    Read(kbd,C);
  Until LotusRec.RecType = 1;
  Close(F);
  Writeln('Create a sample WKS file... ? Y/N');
  Write('It will be called TEST2.WKS');
  Read(Ch);
  If UpCase(CH) = 'Y' Then
  Begin
    Assign(F,'TEST2.WKS');
    ReWrite(F);
    WriteBeginFile(F);
    WriteWksSize(F,2,0);
    WriteIntegerCell(F,255,0,0,1);
    WriteIntegerCell(F,255,1,0,2);
    WriteRealCell(F,255,2,0,1.123);
    EndLotusFile(F);
  End;
End.